"""mysql数据库连接池"""

import logging
import threading
from threading import Lock
from typing import List

import pymysql
from dbutils.pooled_db import PooledDB

logger = logging.getLogger()


class MysqlPool:
    __pool = None  # 类属性，所有实例公用变量
    __lock = Lock()

    def __init__(
            self,
            mincached=10,
            maxcached=20,
            maxshared=10,
            maxconnections=200,
            blocking=True,
            maxusage=100,
            setsession=None,
            reset=True,
            config: dict = {},
            charset='utf8mb4'
    ) -> None:  # sourcery skip: default-mutable-arg
        """数据库接口

        :param mincached: 连接池中空闲连接的初始数量
        :param maxcached: 连接池中空闲连接的最大数量
        :param maxshared: 共享连接的最大数量
        :param maxconnections: 创建连接池的最大数量
        :param blocking: 超过最大连接数量时的表现，True等待连接数量下降，False直接报错处理
        :param maxusage: 单个连接的最大重复使用次数
        :param setsession: optional list of SQL commands that may serve to prepare the session # noqa:E501
        :param reset: how connections should be reset when returned to the pool (False or None to rollback transcations started with begin()  # noqa:E501
        :param config: 数据库连接配置
        :param charset: 字符编码
        """
        with self.__lock:
            if not self.__pool:
                self.__class__.__pool = PooledDB(
                    pymysql,
                    mincached,
                    maxcached,
                    maxshared,
                    maxconnections,
                    blocking,
                    maxusage,
                    setsession,
                    reset,
                    host=config.get('host'),
                    port=config.get('port'),
                    db=config.get('database'),
                    user=config.get('username'),
                    passwd=config.get('password'),
                    charset=charset
                )
        self._conn = self.__pool.connection()  # type: ignore
        if self._conn:
            self._cursor_tuple = self._conn.cursor()
            self._cursor_dict = self._conn.cursor(
                cursor=pymysql.cursors.DictCursor  # type: ignore
            )
        # 线程互斥锁
        self._mutex = threading.Lock()

    def __new__(cls, *args, **kw):
        if not hasattr(cls, '_instance'):
            cls._instance = super().__new__(cls)
        return cls._instance

    def close(self):
        """关闭数据库连接"""
        if self._conn:
            try:
                if type(self._cursor_dict == 'object'):
                    self._cursor_dict.close()
                if type(self._cursor_tuple == 'object'):
                    self._conn.close()
            except Exception as e:
                logger.error('关闭数据库连接', e)

    def query_tuple(self, sql):
        """查询sql，返回tuple"""
        query_tuple_res = ''
        try:
            self._cursor_tuple.execute(sql)
            query_tuple_res = self._cursor_tuple.fetchall()
        except Exception as e:
            logger.error('发生异常：', e)
        return query_tuple_res

    def query_dict(self, sql):
        """查询sql，返回list"""
        query_dict_res = ''
        try:
            self._cursor_dict.execute(sql)
            query_dict_res = self._cursor_dict.fetchall()
        except Exception as e:
            logger.error('发生异常：', e)
        return query_dict_res

    def execute(self, sql):
        """执行sql语句，增删改"""
        excute_res = False
        try:
            self._cursor_dict.execute(sql)
            self._conn.commit()
            excute_res = True
        except Exception as e:
            logger.error('发生异常：', e)
            self._conn.rollback()
        return excute_res

    def insert(self, table_name, data) -> int:
        """插入数据"""
        keys = ', '.join(data.keys())
        values = ', '.join(['%s'] * len(data))
        sql = f'INSERT INTO {table_name} ({keys}) VALUES ({values})'
        try:
            rows = self._cursor_dict.execute(sql, tuple(data.values()))
            self._conn.commit()
            return rows
        except Exception as e:
            logger.error(f'数据插入发生异常：{e}, sql: {sql}, data: {data}')
            self._conn.rollback()
        return -1

    def insert_and_update(self, table_name, data: dict) -> int:
        """插入数据，如果数据存在则更新
        insert into user (id, name, password) values ('1','张三','123456')
        on duplicate key update password=values(password);
        """
        keys = ', '.join(data.keys())
        values = ', '.join(['%s'] * len(data))
        update_values = ', '.join([f'{key}=values({key})' for key in data])
        sql = f'INSERT INTO {table_name} ({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE {update_values}'  # noqa:E501

        try:
            with self._mutex:
                logger.info(f'Executing SQL statement:{sql}, Data: {data}')
                rows = self._cursor_dict.execute(sql, tuple(data.values()))
                self._conn.commit()
                return rows
        except Exception as e:
            logger.error(f'数据插入发生异常: {e}, sql: {sql}, data: {data}')
            self._conn.rollback()
        return -1

    def insert_many(self, table_name, data: List[dict]) -> int:
        """批量插入数据
        # TODO 批量插入切割 for i in range(0, len(data), 20): data_list[i:i+seg]
        """
        keys = ', '.join(data[0].keys())
        values = ', '.join(['%s'] * len(data[0]))
        update_values = ', '.join([f'{key}=values({key})' for key in data[0].keys()])  # noqa:E501
        sql = f'INSERT INTO {table_name} ({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE {update_values}'  # noqa:E501

        _data = [tuple(i.values()) for i in data]
        try:
            rows = self._cursor_dict.executemany(sql, _data)
            self._conn.commit()
            return rows
        except Exception as e:
            logger.error(f'数据插入发生异常：{e}, sql: {sql}, data: {_data}')
            self._conn.rollback()
        return -1
